<?php
function getProteinReport($org,$ref) {
    $file = "data/p3db-".getP3DBVersion()."-phosphoprotein-report";

    $query = "SELECT id,annotation, sequence,latin ".
             "FROM protein, organism ".
             "WHERE protein.organism=organism.label";
    if (!empty($org)) {
        $file .= '_'.appendixOrg($org);
        $query .= " AND protein.organism='$org'";
    }
    if (!empty($ref)) {
        $file .= '_'.appendixRef($ref);
        $query .= " AND EXISTS (SELECT * FROM proteinDataSourceRelation WHERE proteinDataSourceRelation.protein=protein.id AND proteinDataSourceRelation.datasource='$ref')";
    }

    $file .= ".gz";

    if (file_exists($file))
        return $file;

    $result = mysql_query($query) or die("Error, query failed: $query");

    $zp = gzopen($file, 'w9');
//    gzwrite($zp, "P3DB Protein ID\tXref\tAnnotation\tSequence\tOrganism\tNo. of Spectra\tPubMed\n");
    gzwrite($zp, "P3DB Protein ID\tXref\tAnnotation\tSequence\tOrganism\tPubMed\n");

    while ($row = mysql_fetch_array($result)) {
        $id = $row['id'];
        $xref = implode(';',getXref($id));
        $pubmed = formatDataSources(getDataSourceProtein($id));
        $ann = $row['annotation'];
        $seq = $row['sequence'];
        $org = $row['latin'];
//        $spec = spectralCountProtein($id, $ref);
//        gzwrite($zp, "$id\t$xref\t$ann\t$seq\t$org\t$spec\t$pubmed)\n");
        gzwrite($zp, "$id\t$xref\t$ann\t$seq\t$org\t$pubmed\n");
    }

    gzclose($zp);

    return $file;
}

function getSiteReport($org,$ref) {
    $file = "data/p3db-".getP3DBVersion()."-phosphosite-report";
    $query = "SELECT id,protein,locationInProtein FROM site WHERE ptm='p'";
    if (!empty($org)) {
        $file .= '_'.appendixOrg($org);
        $query .= " AND '$org'=(SELECT organism FROM protein WHERE protein.id=site.protein)";
    }
    if (!empty($ref)) {
        $file .= '_'.appendixRef($ref);
        $query .= " AND EXISTS (SELECT * FROM siteDataSourceRelation WHERE siteDataSourceRelation.site=site.id AND siteDataSourceRelation.datasource='$ref')";
    }

    $file .= ".gz";

    if (file_exists($file))
        return $file;

    $result = mysql_query($query) or die("Error, query failed: $query");

    $zp = gzopen($file, 'w9');
//    gzwrite($zp, "P3DB Protein ID\tXref\tP3DB Site ID\tLocation\tNo. of Spectra\tPubMed\n");
    gzwrite($zp, "P3DB Protein ID\tXref\tP3DB Site ID\tLocation\tPubMed\n");

    while ($row = mysql_fetch_array($result)) {
        $id = $row['id'];
        $pubmed = formatDataSources(getDataSourceSite($id));
        $protein = $row['protein'];
        $xref = implode(';',getXref($protein));
        $loc = $row['locationInProtein']+1;
//        $spec = spectralCountSite($id, $ref);
//        gzwrite($zp, "$protein\t$xref\t$id\t$loc\t$spec\t$pubmed\n");
        gzwrite($zp, "$protein\t$xref\t$id\t$loc\t$pubmed\n");
    }

    gzclose($zp);

    return $file;
}

function getNrPepReport($org,$ref) {
    $file = "data/p3db-".getP3DBVersion()."-nr-phosphopeptide-report";
    $query = "SELECT DISTINCT phosphoSequence,protein,location FROM peptide WHERE 1";
    if (!empty($org)) {
        $file .= '_'.appendixOrg($org);
        $query .= " AND '$org'=(SELECT organism FROM protein WHERE protein.id=peptide.protein)";
    }
    if (!empty($ref)) {
        $file .= '_'.appendixRef($ref);
        $query .= " AND '$ref'=(SELECT dataSource FROM mass WHERE mass.id=peptide.mass)";
    }

    $file .= ".gz";

    if (file_exists($file))
        return $file;

    $result = mysql_query($query) or die("Error, query failed: $query");

    $zp = gzopen($file, 'w9');
//    gzwrite($zp, "P3DB Protein ID\tXref\tPhosphopeptide\tLocation\tNo. of Spectra\tPubMed\n");
    gzwrite($zp, "P3DB Protein ID\tXref\tPhosphopeptide\tLocation\n");

    while ($row = mysql_fetch_array($result)) {
        $protein = $row['protein'];
        $xref = implode(';',getXref($protein));
        $loc = $row['location']+1;
        $nrseq = $row['phosphoSequence'];
//        $pubmed = formatDataSources(getDataSourceNrPep($protein,$loc-1,$nrseq));
//        $spec = spectralCountNrPep($protein,$loc-1,$nrseq, $ref);
//        gzwrite($zp, "$protein\t$xref\t$nrseq\t$loc\t$spec\t$pubmed\n");
        gzwrite($zp, "$protein\t$xref\t$nrseq\t$loc\n");
    }

    gzclose($zp);

    return $file;
}

function getSpectrumReport($org,$ref) {
    $file = "data/p3db-".getP3DBVersion()."-phosphopeptide-report";
    $query = "SELECT peptide.id,phosphoSequence,protein,location,mass.id as msid,file,scan,reference,pubmed FROM peptide,mass,dataSource WHERE peptide.mass=mass.id AND mass.dataSource=dataSource.id";
    if (!empty($org)) {
        $file .= '_'.appendixOrg($org);
        $query .= " AND '$org'=(SELECT organism FROM protein WHERE protein.id=peptide.protein)";
    }
    if (!empty($ref)) {
        $file .= '_'.appendixRef($ref);
        $query .= " AND '$ref'=dataSource";
    }

    $file .= ".gz";

    if (file_exists($file))
        return $file;

    $zp = gzopen($file, 'w9');
    gzwrite($zp, "P3DB Protein ID\tXref\tP3DB Peptide ID\tSpectrum\tPhosphopeptide\tLocation\tPubMed or Reference");
    $params = getRecordsFromATable("peptideParameterType","1");
    foreach ($params as $param) {
        gzwrite($zp, "\t".$param['name']);
    }
    gzwrite($zp, "\n");

    $pepparams = getPeptideParamInMap($org, $ref);

    $result = mysql_query($query) or die("Error, query failed: $query");

    while ($row = mysql_fetch_array($result)) {
        $protein = $row['protein'];
        $xref = implode(';',getXref($protein));
        $id = $row['id'];
        $loc = $row['location']+1;
        $nrseq = $row['phosphoSequence'];
        $reference = $row['pubmed'];
        if (empty($reference))
            $reference = $row['reference'];
        if (isset($row['scan']))
            $scan = $row['file'].'.'.$row['scan'];
        else
            $scan = $row['msid'];
        gzwrite($zp, "$protein\t$xref\t$id\t$scan\t$nrseq\t$loc\t$reference");
        foreach ($params as $param) {
            gzwrite($zp, "\t");
            gzwrite($zp, $pepparams[$id][$param['id']]);
        }
        gzwrite($zp, "\n");
    }

    gzclose($zp);

    return $file;
}

function getPeptideParamInMap($org,$ref) {
    $query = "SELECT * FROM peptideParameter WHERE 1";
    if (!empty($org)) {
        $query .= " AND '$org'=(SELECT organism FROM peptide,protein WHERE peptideParameter.peptide=peptide.id AND protein.id=peptide.protein)";
    }
    if (!empty($ref)) {
        $query .= " AND '$ref'=(SELECT dataSource FROM peptide,mass WHERE peptideParameter.peptide=peptide.id AND mass.id=peptide.mass)";
    }

    $ret = array();
    $result = mysql_query($query) or die('Error, query failed'.$query);
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $ret[$row['peptide']][$row['parameter']] = $row['value'];
//        $pepp = $ret[$row['peptide']];
//        if (!isset($pepp))
//            $pepp = array();
//        $pepp[$row['parameter']] = $row['value'];
    }
    return $ret;
}

function formatDataSources($dataSources) {
    $pubmed = array();
    foreach ($dataSources as $ds) {
        $pubmed[] = $ds['pubmed'];
    }
    return implode(';', $pubmed);
}

function appendixOrg($org) {
    if (empty($org))
        return "";
    $query = "SELECT latin FROM organism WHERE label='$org'";
    $result = mysql_query($query) or die("Error, query failed: $query");
    $row = mysql_fetch_array($result);
    $latin = $row['latin'];
    return str_replace(' ', '-', $latin);
}

function appendixRef($ref) {
    if (empty($ref))
        return "";
    $query = "SELECT pubmed FROM dataSource WHERE id='$ref'";
    $result = mysql_query($query) or die("Error, query failed: $query");
    $row = mysql_fetch_array($result);
    $pubmed = $row['pubmed'];
    if (!empty($pubmed))
        return "pubmed-$pubmed";
    return $ref;
}

?>
